import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
# from fastapi import Request
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

check_plan_head_router = APIRouter(prefix="/inspection/checkplanHead", tags=["巡查计划管理"],
                                   dependencies=[Depends(oauth2_scheme)])


class CheckPlanSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftakedateStart: Optional[str] = Field("", description="开始日期(yyyyMMdd)")
    ftakedateEnd: Optional[str] = Field("", description="结束日期(yyyyMMdd)")
    fpointNo: Optional[str] = Field("", description="点位编号")
    fbillno: Optional[str] = Field("", description="单据编号（支持前缀匹配）")
    fplanuser: Optional[str] = Field("", description="计划负责人")
    fchecktypeno: Optional[str] = Field("", description="检查类型编号")
    fbillstate: Optional[str] = Field("", description="单据状态编码")


@check_plan_head_router.get("/list", summary="巡查计划查询接口")
def list_check_plan(search: CheckPlanSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.s_tc_checkplan_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fchecktypeno != "":
        sql = sql + " and fchecktypeno = :fchecktypeno "
        param["fchecktypeno"] = search.fchecktypeno
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fplanuser != "":
        sql = sql + " and fplanuser = :fplanuser "
        param["fplanuser"] = search.fplanuser
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fplandate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fplanuser,fplandate,fpoint_no,fchecktypeno,fequipmentbarcode,feuipmentid,"
    sql = sql + " fplannote,fchecknote,fupddate,foperator,fcheckdate,fcheckuser,fbillstate"
    sql = sql + " from lims.s_tc_checkplan_head where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fchecktypeno != "":
        sql = sql + " and fchecktypeno = :fchecktypeno "
        param["fchecktypeno"] = search.fchecktypeno
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fplanuser != "":
        sql = sql + " and fplanuser = :fplanuser "
        param["fplanuser"] = search.fplanuser
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fplandate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    if search.order == "desc":
        sql = sql + " order by  fplandate,fbillno desc "
    else:
        sql = sql + " order by  fplandate,fbillno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    # 巡查照片
    param = {}
    sql_2 = " select b.fbillno,b.fline,b.fflag,b.fimage"
    sql_2 = sql_2 + " from lims.s_tc_checkplan_head a,lims.s_tc_checkplan_detail2 b where a.fbillno=b.fbillno "
    if search.fsiteNo != "":
        sql_2 = sql_2 + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql_2 = sql_2 + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbillno != "":
        sql_2 = sql_2 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fchecktypeno != "":
        sql_2 = sql_2 + " and fchecktypeno = :fchecktypeno "
        param["fchecktypeno"] = search.fchecktypeno
    if search.fplanuser != "":
        sql_2 = sql_2 + " and fplanuser = :fplanuser "
        param["fplanuser"] = search.fplanuser
    if search.fbillstate != "":
        sql_2 = sql_2 + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql_2 = sql_2 + " and to_char(fplandate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    sql_2 = sql_2 + " order by b.fbillno,b.fline"
    data_detail2 = db.execute(text(sql_2), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fcheckitemno,b.fmemo"
    sql_3 = sql_3 + " from lims.s_tc_checkplan_head a,lims.s_tc_checkplan_detail b where a.fbillno=b.fbillno "
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fpointNo != "":
        sql_3 = sql_3 + " and fpoint_no = :fpointNo "
        param["fpointNo"] = search.fpointNo
    if search.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fchecktypeno != "":
        sql_3 = sql_3 + " and fchecktypeno = :fchecktypeno "
        param["fchecktypeno"] = search.fchecktypeno
    if search.fplanuser != "":
        sql_3 = sql_3 + " and fplanuser = :fplanuser "
        param["fplanuser"] = search.fplanuser
    if search.fbillstate != "":
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(fplandate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fplanuser'] = data[key][2]
        d1['fplandate'] = data[key][3]
        d1['fpointNo'] = data[key][4]
        d1['fchecktypeno'] = data[key][5]
        d1['fequipmentbarcode'] = data[key][6]
        d1['feuipmentid'] = data[key][7]
        d1['fplannote'] = data[key][8]
        d1['fchecknote'] = data[key][9]
        d1['fupddate'] = data[key][10]
        d1['foperator'] = data[key][11]
        d1['fcheckdate'] = data[key][12]
        d1['fcheckuser'] = data[key][13]
        d1['fbillstate'] = data[key][14]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fcheckitemno'] = data_detail[j1][2]
                d2['fmemo'] = data_detail[j1][3]
                detail_list.append(d2)

        detail_list2 = []
        for j2 in range(len(data_detail)):
            if (str(data_detail2[j2][0]) == str(sbillno)):
                d3 = {}
                d3['fbillno'] = data_detail[j2][0]
                d3['fline'] = data_detail[j2][1]
                d3['fflag'] = data_detail[j2][2]
                d3['fimage'] = data_detail[j2][3]
                detail_list2.append(d3)

        d1["details"] = detail_list
        d1["details2"] = detail_list2
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
